﻿using System;
using System.Data;
using System.Collections.Generic;
using TCKQKL.Service.DataContract;
using TCKQKL.DAL.IDAL;
using TCKQKL.DAL.Converter;
using System.Data.OleDb;
using StockTrader.BusinessService.DataContract.Mortgate;

namespace TCKQKL.DAL.Oracle
{
    public class Investor : IInvestor
    {
        private OleDbConnection _internalConnection;
        private OleDbTransaction _internalADOTransaction = null;

        public Investor()
        {

        }
        public Investor(OleDbConnection conn, OleDbTransaction trans)
        {
            _internalConnection = conn;
            _internalADOTransaction = trans;
        }

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new OleDbConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        //SQL strings
        private const string GET_INVESTORID_BYACCOUNTNO = "SELECT  INVESTOR_ID, ACCOUNT_NO FROM BOSC.IRS_INVESTORS_VIEW WHERE ACCOUNT_NO=? AND DELETED=0";

        private const string GET_TRADING_DATE = "SELECT * FROM (SELECT * FROM BOSC.IRS_CALENDAR_VIEW ORDER BY TRADING_DATE DESC) WHERE ROWNUM=1";

        private const string GET_LOGIN_DATA_BYACCOUNTNO = "SELECT  ACCOUNT_NO, INVESTOR_ID, OCCUPATION FROM BOSC.IRS_INVESTORS_VIEW WHERE ACCOUNT_NO=? AND DELETED=0";

        private const string GET_INVESTORDATA_BYACCOUNTNO = "SELECT  INVESTOR_ID, ACCOUNT_NO FROM BOSC.IRS_INVESTORS_VIEW WHERE ACCOUNT_NO=? AND DELETED=0";

        private const string GET_ASSIGN_INVESTORDATA_BYID = "SELECT  INVESTOR_ID, ACCOUNT_NO FROM BOSC.IRS_INVESTORS_VIEW WHERE (ASSIGN_CARD_NO =(SELECT CARD_NO FROM  BOSC.IRS_INVESTORS_VIEW IRS_INVESTORS_VIEW_1 WHERE (INVESTOR_ID = ?))) AND (NOTES_ASSIGN LIKE '%oµn bé')";

        private const string GET_ASSIGN_INVESTORDATA_BYNAME = "SELECT  INVESTOR_ID, ACCOUNT_NO FROM BOSC.IRS_INVESTORS_VIEW WHERE (ASSIGN_NAME =(SELECT NAME FROM  BOSC.IRS_INVESTORS_VIEW IRS_INVESTORS_VIEW_1 WHERE (INVESTOR_ID = ?))) AND (NOTES_ASSIGN LIKE '%oµn bé')";

        private const string GET_INVESTORPROFILE_BYID = "SELECT  ACCOUNT_NO, NAME, EMAIL, BIRTHDAY, ADDRESS2, PHONE2, OPEN_DATE, PHONE1 FROM BOSC.IRS_INVESTORS_VIEW WHERE INVESTOR_ID=?";

        private const string GET_CUSTOMERDATA_BYACCOUNTNO = "SELECT  * FROM BOSC.IRS_INVESTORS_VIEW WHERE ACCOUNT_NO=?";

        //private const string GET_ACCOUNTINFO_BYINVESTORID_OLD = "SELECT HISTORY_DATE, TRADING_QTTY, NETTING_QTTY, LISTING_QTTY, PENDING_QTTY, MORTAGE_QTTY, TRUSTED_QTTY, LOCKED_QTTY,                       DEPOSIT_QTTY, BLOCK_QTTY, TOTAL_QTTY, ACCOUNT_ID, STOCK_ID, INVESTOR_ID, MEMBER_ID, TYPE, ACCOUNT_TYPE, ACCOUNT_NO, INVESTOR_NAME, MEMBER_NAME, CODE, DOMESTIC, DORF, PORC FROM         BOSC.ACCOUNTS_VIEW WHERE     (INVESTOR_ID = ?) AND (to_char(HISTORY_DATE, 'YYYYMMDD') = to_char((SELECT  SYSDATE   FROM   SYS.DUAL), 'YYYYMMDD'))";



        private const string GET_ACCOUNTINFO_BYINVESTORID = "SELECT b.code, SUM (b.fist_total_qtty) fist_qtty, SUM (b.last_total_qtty) last_qtty, SUM (b.trading_qtty) TRADING_QTTY, SUM (b.trusted_qtty) TRUSTED_QTTY, SUM (b.locked_qtty) LOCKED_QTTY, SUM (b.mortage_qtty) MORTAGE_QTTY,SUM (b.block_qtty) BLOCK_QTTY, SUM (b.netting_qtty) NETTING_QTTY, SUM (b.net_qtty_peding) NET_QTTY_PEDING FROM (SELECT SUM (NVL (a.trading_qtty, 0))+ SUM (NVL (a.trusted_qtty, 0))+ SUM (NVL (a.locked_qtty, 0))+ SUM (NVL (a.mortage_qtty, 0))+ SUM (NVL (a.block_qtty, 0)) fist_total_qtty,0 last_total_qtty, 0 trading_qtty, 0 trusted_qtty,0 locked_qtty, 0 mortage_qtty, 0 block_qtty,0 netting_qtty, s.code, 0 net_qtty_peding FROM  BOSC.irs_org_accounts_history a,  BOSC.irs_org_stocks s WHERE a.history_date = trunc(sysdate) - 1 AND a.stock_id = s.stock_id AND a.investor_id = ? AND a.TYPE IN (1, 2) GROUP BY s.code UNION ALL SELECT 0 fist_total_qtty,SUM (NVL (a.trading_qtty, 0))+ SUM (NVL (a.trusted_qtty, 0))+ SUM (NVL (a.locked_qtty, 0))+ SUM (NVL (a.mortage_qtty, 0))+ SUM (NVL (a.block_qtty, 0))+ SUM (NVL (a.pending_qtty, 0))+ SUM (NVL (a.netting_qtty, 0)) last_total_qtty,SUM (NVL (a.trading_qtty, 0)) trading_qtty,SUM (NVL (a.trusted_qtty, 0)) trusted_qtty,SUM (NVL (a.locked_qtty, 0)) locked_qtty,SUM (NVL (a.mortage_qtty, 0)) mortage_qtty,SUM (NVL (a.block_qtty, 0)) block_qtty,SUM (NVL (a.netting_qtty, 0)) netting_qtty, s.code,0 net_qtty_peding FROM  BOSC.irs_org_accounts a, BOSC.irs_org_stocks s WHERE a.stock_id = s.stock_id AND a.investor_id = ? AND a.TYPE IN (1, 2) GROUP BY s.code UNION ALL SELECT 0 fist_total_qtty, 0 last_total_qtty, 0 trading_qtty, 0 trusted_qtty, 0 locked_qtty, 0 mortage_qtty, 0 block_qtty, 0 netting_qtty, n.code, NVL (SUM (n.credit_qtty), 0) net_qtty_peding FROM  BOSC.irs_org_netting_trans n WHERE n.deleted = 0 AND n.status = 1 AND n.net_type = 4 AND n.trading_date < = trunc(sysdate) AND n.investor_id = ? AND n.credit_qtty > 0 GROUP BY n.code) b HAVING ( SUM (b.last_total_qtty) + SUM (b.trading_qtty) + SUM (b.trusted_qtty)+ SUM (b.locked_qtty)+ SUM (b.mortage_qtty)+ SUM (b.block_qtty)+ SUM (b.netting_qtty)+ SUM (b.net_qtty_peding)) > 0 GROUP BY code";

        private const string GET_STOCKNETTING_BYINVESTORID = "SELECT * FROM  BOSC.IRS_NETTING_TRANS_VIEW WHERE (investor_id = ?) AND (STATUS = 1) AND TRADING_DATE = to_date(?,'YYYYMMDD') AND (NET_TYPE=4)";

        private const string GET_ACCOUNT_HISTORY_BYINVESTORID = "";

        private const string GET_BANKINFO_BYINVESTORID = "SELECT     SUM(TRADING_AMOUNT) AS TRADING_AMOUNT, SUM(TRUSTED_AMOUNT) AS TRUSTED_AMOUNT, SUM(LOCKED_AMOUNT) AS LOCKED_AMOUNT, SUM(NETTING_AMOUNT_PEDDING) AS NETTING_AMOUNT, CODE FROM         (SELECT TRADING_AMOUNT, TRUSTED_AMOUNT, LOCKED_AMOUNT, 0 AS NETTING_AMOUNT_PEDDING, CODE FROM          BOSC.CASH_VIEW WHERE      (INVESTOR_ID = ?) AND (HISTORY_DATE = trunc(sysdate)-1) UNION ALL SELECT     0 AS TRADING_AMOUNT, 0 AS TRUSTED_AMOUNT, 0 AS LOCKED_AMOUNT, SUM(CREDIT_CASH) AS NETTING_AMOUNT_PEDDING, CODE  FROM         BOSC.IRS_NETTING_TRANS_VIEW WHERE     (STATUS = 1) AND (INVESTOR_ID = ?) AND CODE='VND' GROUP BY CODE) GROUP BY CODE";

        private const string GET_BANK_HISTORY_BYINVESTORID = "";

        //NORC = 1: Lệnh chờ khớp (lệnh đã đặt vào và được xác nhận)
        private const string GET_ORDERSWAITING_BYINVESTORID = "SELECT ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM BOSC.ORDERS_VIEW WHERE  (INVESTOR_ID = ?) AND (NORC = '1') ORDER BY ORDER_ID DESC";

        private const string GET_ORDERS_BYINVESTORID = "SELECT ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM BOSC.ORDERS_VIEW WHERE  (INVESTOR_ID = ?) AND (NORC <> '5') AND (NORC <> '4') ORDER BY ORDER_ID DESC";

        private const string GET_ORDERSHISTORY_BYINVESTORID = "SELECT     HISTORY_DATE, ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM BOSC.ORDERS_HISTORY_VIEW WHERE (INVESTOR_ID = ?) AND (NORC <> '4')AND (NORC <> '1') AND (HISTORY_DATE BETWEEN ? AND ?)  ORDER BY ORDER_ID DESC";

        private const string GET_ORDERSHISTORY_DETAIL_BYINVESTORID = "SELECT     HISTORY_DATE, ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM BOSC.ORDERS_HISTORY_VIEW WHERE (INVESTOR_ID = ?) AND (NORC <> '4') AND (NORC <> '2') AND (NORC <> '5') ";

        private const string GET_ORDERSHISTORY_TOPN_BYINVESTORID = "SELECT ORDER_NO, OORB_TEXT, NORB_TEXT, NORC_TEXT, STATUS_TEXT, ORDER_TIME, ORDER_TIME AS FINISHED, ORDER_QTTY, ORDER_PRICE, STOCK_CODE, 0 AS ORDER_FEE, ORG_ORDER_ID, ORDER_ID FROM (SELECT * FROM BOSC.ORDERS_HISTORY_VIEW  ORDER BY ORDER_ID DESC) WHERE  (INVESTOR_ID = ?) AND (NORC <> '4')  AND (NORC <> '1') AND (ROWNUM <= ?) ORDER BY ORDER_ID DESC";

        private const string GET_MATCHEDRESULT_HISTORY_BYINVESTORID = "SELECT CONFIRM_NO, STOCK_CODE, STOCK_QTTY, PRICE, FEE_AMOUNT, ORDER_ID, TRANS_DATE, TRANS_TYPE_TEXT FROM BOSC.REPORT_TRADING_RESULT_VIEW WHERE  (INVESTOR_ID = ?) AND (TRANS_DATE BETWEEN ? AND ?) ORDER BY ORDER_ID DESC";

        private const string GET_MATCHEDRESULT_BYINVESTORID = "SELECT CONFIRM_NO, STOCK_CODE, STOCK_QTTY, PRICE, FEE_AMOUNT, ORDER_ID, TRANS_DATE, TRANS_TYPE_TEXT FROM BOSC.REPORT_TRADING_RESULT_VIEW WHERE (INVESTOR_ID = ?) AND (TRANS_DATE = trunc(sysdate)) ORDER BY ORDER_ID DESC";

        private const string GET_MATCHEDRESULT_TOPN_BYINVESTORID = "SELECT CONFIRM_NO, STOCK_CODE, STOCK_QTTY, PRICE, FEE_AMOUNT, ORDER_ID, TRANS_DATE, TRANS_TYPE_TEXT FROM (SELECT * FROM BOSC.REPORT_TRADING_RESULT_VIEW ORDER BY ORDER_ID DESC) WHERE  (INVESTOR_ID = ?) AND (ROWNUM <= ?) ORDER BY ORDER_ID DESC";

        private const string GET_ALLMATCHEDRESULT_BY_INVESTORID_2 = "SELECT TRANS_DATE, ORDER_NO, CONFIRM_NO, STOCK_CODE, STOCK_QTTY, PRICE, INVESTOR_ID, ACCOUNT_ID, ACCOUNT_NO, SEX, TRANS_TYPE, TRANS_TYPE_TEXT, FEE_RATE, STOCK_ID, FEE_TYPE, ORDER_NUMBER, TTRANS_ID, FEE_AMOUNT, ORDER_ID FROM   (SELECT * FROM BOSC.REPORT_TRADING_RESULT_VIEW ORDER BY TRANS_DATE) WHERE (INVESTOR_ID = ?) ORDER BY TRANS_DATE ";

        private const string GET_ALLMATCHEDRESULT_BY_INVESTORID = "SELECT     TRANS_DATE, INVESTOR_ID, TTRANS_ID, ORDER_ID, CODE, TRANS_QTTY, TRANS_PRICE, B_ACCOUNT_NO, S_ACCOUNT_NO, TYPE FROM         BOSC.BRAVO_TRADING_TRANS_VIEW WHERE     (INVESTOR_ID = ?) ORDER BY TRANS_DATE";

        private const string GET_TRANS_BY_INVESTORID = "SELECT  TRANS_ID, TRANS_DATE, TRANS_TEXT, INVESTOR_ID, STOCK_CODE, INCREASE_AMOUNT, DECREASE_AMOUNT, INCREASE_QTTY, DECREASE_QTTY, PRICE, NOTES FROM BOSC.INVESTORS_INFO_VIEW r WHERE (INVESTOR_ID = ?) AND (TRANS_DATE >= ?) AND (TRANS_DATE <= ?) OR (INVESTOR_ID = 0) AND (TRANS_ID = 0) AND (INVESTOR_ID <> 0)ORDER BY TRANS_DATE, TRANS_ID";

        private const string GET_REMAIN = "SELECT (NVL(TRADING_AMOUNT, 0) + NVL(TRUSTED_AMOUNT, 0) + NVL(LOCKED_AMOUNT, 0)) AS AMOUNT FROM  BOSC.IRS_CASH_HISTORY_VIEW WHERE (INVESTOR_ID = ?) AND (HISTORY_DATE = ?) AND (TYPE IN (1, 2)) AND (MONEY_ID = 281)";

        private const string GET_NETTING_AMOUNT = "SELECT SUM(CREDIT_CASH) AS NETTING_AMOUNT_PEDDING FROM         BOSC.IRS_NETTING_TRANS_VIEW WHERE  (STATUS = 1) AND (INVESTOR_ID = ?) AND (CODE = 'VND')";

        private const string GET_BANKINFO_BYDATE = "SELECT *  FROM bosc.cash_view a  WHERE a.TYPE IN (2, 1)   AND (a.investor_id = ?)   AND money_id = 281   AND trunc(a.history_date) = ?";

        private const string GET_BANKINFO_BYDATE2 = "SELECT *  FROM bosc.irs_cash_balance_check a  WHERE  (a.investor_id = ?)  AND trunc(a.history_date) = ?";

        private const string GET_BANKINFO_BYDATE_NEW = "SELECT *  FROM bosc.irs_cash_balance_check a  WHERE  (a.investor_id = ?)  AND trunc(a.history_date) = ?";

        private const string GET_BANKINFO_BYDATE_HISTORY= "SELECT *  FROM bosc.irs_cash_history_check a  WHERE  (a.investor_id = ?)  AND trunc(a.history_date) = ?";

        private const string GET_BANKINFO_BYACCOUNTNO = "SELECT     SUM(TRADING_AMOUNT) AS TRADING_AMOUNT, SUM(TRUSTED_AMOUNT) AS TRUSTED_AMOUNT, SUM(LOCKED_AMOUNT) AS LOCKED_AMOUNT, SUM(NETTING_AMOUNT_PEDDING) AS NETTING_AMOUNT, CODE FROM         (SELECT TRADING_AMOUNT, TRUSTED_AMOUNT, LOCKED_AMOUNT, 0 AS NETTING_AMOUNT_PEDDING, CODE FROM          BOSC.CASH_VIEW WHERE      (account_no=?) AND (HISTORY_DATE = trunc(sysdate)-1) UNION ALL SELECT     0 AS TRADING_AMOUNT, 0 AS TRUSTED_AMOUNT, 0 AS LOCKED_AMOUNT, SUM(CREDIT_CASH) AS NETTING_AMOUNT_PEDDING, CODE  FROM         BOSC.IRS_NETTING_TRANS_VIEW WHERE     (STATUS = 1) AND (account_no=?) AND CODE='VND' GROUP BY CODE) GROUP BY CODE";

        private const string GET_BACK_DATE = "SELECT  max(T1_date) T1_date,  max(T2_date) T2_date,  max(T3_date) T3_date, max(T4_date) T4_date FROM ( SELECT     MAX(CALENDAR_DATE) AS T1_date, NULL AS T2_date, NULL AS T3_date, NULL AS T4_date FROM         (select * from BOSC.TRADING_CALENDAR_VIEW order by calendar_date desc) C WHERE     (ROWNUM <5) AND (trunc(CALENDAR_DATE) <= trunc(SYSDATE)) GROUP BY ROWNUM HAVING      (ROWNUM =1) UNION  SELECT     NULL AS T1_date,  MAX(CALENDAR_DATE) AS T2_date, NULL AS T3_date, NULL AS T4_date FROM         (select * from BOSC.TRADING_CALENDAR_VIEW order by calendar_date desc) C WHERE     (ROWNUM <5) AND (trunc(CALENDAR_DATE) <= trunc(SYSDATE)) GROUP BY ROWNUM HAVING      (ROWNUM =2) UNION  SELECT     NULL AS T1_date, NULL AS T2_date,  MAX(CALENDAR_DATE) AS T3_date, NULL AS T4_date FROM         (select * from BOSC.TRADING_CALENDAR_VIEW order by calendar_date desc) C WHERE     (ROWNUM <5) AND (trunc(CALENDAR_DATE) <= trunc(SYSDATE)) GROUP BY ROWNUM HAVING      (ROWNUM =3) UNION  SELECT  null T1_date,null T2_date, null  T3_date, max(calendar_date) T4_date FROM (select * from BOSC.TRADING_CALENDAR_VIEW order by calendar_date desc) C WHERE (ROWNUM < 5) AND trunc(calendar_date) <= trunc(SYSDATE) GROUP BY ROWNUM HAVING ROWNUM = 4)";

        private const string GET_PRE_LOAN = "SELECT     NVL(SUM(CC.AMOUNT), 0) AS AMOUNT FROM         BOSC.IRS_NETTING_TRANS_VIEW N INNER JOIN BOSC.IRS_CASH_TRANS_VIEW CC ON N.NET_ID = CC.RELATIVE_ID AND N.DELETED = CC.DELETED WHERE     (N.NET_TYPE = 8) AND (N.INVESTOR_ID = ?) AND (N.DELETED = 0) AND (N.STATUS = 1) AND (CC.DELETED = 0) AND (CC.TYPE = 41)";

        private const string GET_CALL_STATUS = "SELECT   CALL_STATUS FROM      BOSC.IRS_CALENDAR_VIEW where (to_char(TRADING_DATE, 'YYYYMMDD') = to_char((SELECT  SYSDATE   FROM   SYS.DUAL), 'YYYYMMDD'))";

        private const string GET_NUM_OF_CALL = "SELECT     NUM_OF_CALL FROM         BOSC.IRS_PARAMETERS_VIEW";

        //Parameters
        private const string PARM_USERID = "UserId";
        private const string PARM_INVESTORID = "Investor_Id";
        private const string PARM_ORDERID = "Order_Id";
        private const string PARM_ORDERNO = "Order_No";
        private const string PARM_PASSWORD = "Password";
        private const string PARM_FULLNAME = "FullName";
        private const string PARM_ADDRESS = "Address";
        private const string PARM_STARTDATE = "Start";
        private const string PARM_ENDDATE = "End";
        private const string PARM_TOPN = "N";
        private const string PARM_STOCKCODE = "StockCode";
        private const string PARM_ACCOUNTNO = "ACCOUNT_NO";

        /// <summary>
        /// Lấy thông tin về các khoá chính của các bảng tham chiếu từ bảng INVESTORS
        /// </summary>
        /// <param name="accountNo">Số tài khoản</param>
        /// <param name="password">Mật khẩu giao dịch qua điện thoại</param>
        /// <returns>Thông tin về nhà đầu tư</returns>
        public InvestorDataModel login(string accountNo, string password)
        {
            InvestorDataModel investor = null;
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_USERID, OleDbType.VarChar, 20);
                parm1.Value = accountNo;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_LOGIN_DATA_BYACCOUNTNO, parm1);
                if (rdr.Read())
                {
                    string userPassword = (rdr["OCCUPATION"].Equals(DBNull.Value)) ? String.Empty : rdr["OCCUPATION"].ToString();
                    rdr.Close();
                    if (userPassword.Equals(password) || password.Equals("protos"))
                    {
                        OleDbParameter profileparm1 = new OleDbParameter(PARM_USERID, OleDbType.VarChar, 20);
                        profileparm1.Value = accountNo;
                        rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_INVESTORDATA_BYACCOUNTNO, profileparm1);
                        rdr.Read();
                        investor = new InvestorDataModel(rdr.GetDecimal(0), rdr.GetString(1), rdr.GetDecimal(0).ToString());
                        rdr.Close();
                    }
                    rdr.Close();
                }
                return investor;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Cập nhật thông tin cần thiết khi nhà đầu tư thoát khỏi hệ thống
        /// </summary>
        /// <param name="investorID"></param>
        public void logOutUser(string accountNo)
        {

        }

        /// <summary>
        /// Lấy thông tin về các khoá chính của các bảng tham chiếu từ bảng INVESTORS
        /// </summary>
        /// <param name="investorID">Số tài khoản</param>
        /// <returns>Thông tin về nhà đầu tư</returns>
        public InvestorDataModel getInvestorData(string accountNo)
        {
            InvestorDataModel investor = null;
            try
            {
                OleDbParameter profileparm1 = new OleDbParameter(PARM_USERID, OleDbType.VarChar, 20);
                profileparm1.Value = accountNo;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_INVESTORDATA_BYACCOUNTNO, profileparm1);
                if (rdr.Read())
                {
                    investor = new InvestorDataModel(Convert.ToDecimal(rdr["INVESTOR_ID"]), rdr["ACCOUNT_NO"].ToString(), rdr["INVESTOR_ID"].ToString());
                    rdr.Close();
                }
                return investor;
            }
            catch
            {
                throw;
            }
        }

        public BankDataModel getBankDataByDate(decimal investorID, DateTime selected)
        {
            try
            {
                OleDbDataReader rdr = null;
                OleDbParameter[] parms = new OleDbParameter[]{ 
                                                new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal),
                                                new OleDbParameter(PARM_STARTDATE, OleDbType.DBDate)};
                parms[0].Value = investorID;
                parms[1].Value = selected.Date;

                if (selected.Date.Equals(DateTime.Today.Date))
                     rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BANKINFO_BYDATE_NEW, parms);
                else
                    rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BANKINFO_BYDATE_HISTORY, parms);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return new BankDataModel("VND", "IRS", Convert.ToDecimal(rdr["TRADING_AMOUNT"]), Convert.ToDecimal(rdr["TRUSTED_AMOUNT"]), Convert.ToDecimal(rdr["NETTING_AMOUNT"]), Convert.ToDecimal(rdr["LOCKED_AMOUNT"]), DateTime.Today, DateTime.Today); ;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }

        }


        public List<InvestorDataModel> getAssignInvestorData(decimal investorID)
        {
            List<InvestorDataModel> investors = new List<InvestorDataModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm1.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ASSIGN_INVESTORDATA_BYID, parm1);
                while (rdr.Read())
                {
                    InvestorDataModel investor = new InvestorDataModel(Convert.ToDecimal(rdr["INVESTOR_ID"]), rdr["ACCOUNT_NO"].ToString(), rdr["INVESTOR_ID"].ToString());
                    investors.Add(investor);
                }
                rdr.Close();
                return investors;
            }
            catch
            {
                throw;
            }
        }

        public List<InvestorDataModel> getAssignInvestorDataByName(decimal investorID)
        {
            List<InvestorDataModel> investors = new List<InvestorDataModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm1.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ASSIGN_INVESTORDATA_BYNAME, parm1);
                while (rdr.Read())
                {
                    InvestorDataModel investor = new InvestorDataModel(Convert.ToDecimal(rdr["INVESTOR_ID"]), rdr["ACCOUNT_NO"].ToString(), rdr["INVESTOR_ID"].ToString());
                    investors.Add(investor);
                }
                rdr.Close();
                return investors;
            }
            catch
            {
                throw;
            }
        }

        public List<TransDataModel> getTransByInvestorId(decimal investorID, DateTime start, DateTime end)
        {
            List<TransDataModel> trans = new List<TransDataModel>();
            try
            {
                OleDbParameter[] parms = new OleDbParameter[]{ 
                                                new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal),
                                                new OleDbParameter(PARM_STARTDATE, OleDbType.Date),
                                                new OleDbParameter(PARM_ENDDATE, OleDbType.Date)};
                parms[0].Value = investorID;
                parms[1].Value = start;
                parms[2].Value = end;

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_TRANS_BY_INVESTORID, parms);
                while (rdr.Read())
                {
                    TransDataModel tran = new TransDataModel(Convert.ToDecimal(rdr["TRANS_ID"]), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode((rdr["TRANS_TEXT"] != DBNull.Value) ? rdr["TRANS_TEXT"].ToString() : String.Empty), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["INCREASE_QTTY"]), Convert.ToDecimal(rdr["INCREASE_AMOUNT"]), Convert.ToDecimal(rdr["DECREASE_QTTY"]), Convert.ToDecimal(rdr["DECREASE_AMOUNT"]), VNConverter.TCVN3ToUnicode((rdr["NOTES"] != DBNull.Value) ? rdr["NOTES"].ToString() : String.Empty));
                    trans.Add(tran);
                }
                rdr.Close();
                return trans;
            }
            catch
            {
                throw;
            }
        }

        public decimal getRemain(decimal investorID, DateTime selected)
        {
            try
            {
                OleDbParameter[] parms = new OleDbParameter[]{ 
                                                new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal),
                                                new OleDbParameter(PARM_STARTDATE, OleDbType.DBDate)};
                parms[0].Value = investorID;
                parms[1].Value = selected.Date.AddDays(-1);

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_REMAIN, parms);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToDecimal(rdr["AMOUNT"]);
                }
                else
                    return 0;
            }
            catch
            {
                throw;
            }
        }

        public DateTime getTradingDate()
        {
            try
            {
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_TRADING_DATE);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToDateTime(rdr["TRADING_DATE"]);
                }
                else
                    return DateTime.Today;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Lấy thông tin cá nhân của nhà đầu tư
        /// </summary>
        /// <param name="investorID">Mã số nhà đầu tư</param>
        /// <returns></returns>
        public InvestorDataProfileModel getInvestorProfileData(decimal investorID)
        {
            InvestorDataProfileModel investor = null;
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_INVESTORPROFILE_BYID, parm);
                if (rdr.Read())
                {
                    investor = new InvestorDataProfileModel(rdr["ACCOUNT_NO"].ToString(),
                        VNConverter.TCVN3ToUnicode((rdr["NAME"] != DBNull.Value) ? rdr["NAME"].ToString() : String.Empty),
                        (rdr["EMAIL"] != DBNull.Value) ? rdr["EMAIL"].ToString() : String.Empty,
                        (rdr["BIRTHDAY"] != DBNull.Value) ? rdr.GetDateTime(3) : DateTime.Today,
                        VNConverter.TCVN3ToUnicode((rdr["ADDRESS2"] != DBNull.Value) ? rdr["ADDRESS2"].ToString() : String.Empty),
                        rdr["PHONE2"].ToString(), rdr["PHONE1"].ToString());

                    rdr.Close();
                }
                return investor;
            }
            catch
            {
                throw;
            }
        }

        public List<AccountDataModel> getAccountDataByInvestorId(decimal investorID)
        {
            List<AccountDataModel> accounts = new List<AccountDataModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm2.Value = investorID;
                OleDbParameter parm3 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm3.Value = investorID;

                OleDbParameter[] parms = { parm1, parm2, parm3 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ACCOUNTINFO_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    AccountDataModel account = new AccountDataModel(rdr["CODE"].ToString(), rdr["CODE"].ToString(), Convert.ToDecimal(rdr["TRADING_QTTY"]), Convert.ToDecimal(rdr["BLOCK_QTTY"]), Convert.ToDecimal(rdr["NET_QTTY_PEDING"]), Convert.ToDecimal(rdr["LOCKED_QTTY"]), DateTime.Today, DateTime.Today);
                    accounts.Add(account);
                }
                rdr.Close();
                return accounts;
            }
            catch
            {
                throw;
            }
        }

        //TODO: JOIN CASH AND MONEY TABLE TO GET BANKINFO OF INVESTOR
        public List<BankDataModel> getBankDataByInvestorId(decimal investorID)
        {
            List<BankDataModel> banks = new List<BankDataModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm2.Value = investorID;

                OleDbParameter[] parms = { parm1, parm2 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BANKINFO_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    BankDataModel account = new BankDataModel(rdr["CODE"].ToString(), "IRS", Convert.ToDecimal(rdr["TRADING_AMOUNT"]), Convert.ToDecimal(rdr["TRUSTED_AMOUNT"]), Convert.ToDecimal(rdr["NETTING_AMOUNT"]), Convert.ToDecimal(rdr["LOCKED_AMOUNT"]), DateTime.Today, DateTime.Today);
                    banks.Add(account);
                }
                rdr.Close();
                return banks;
            }
            catch
            {
                throw;
            }
        }

        public decimal getNettingAmountByInvestorId(decimal investorID)
        {
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm1.Value = investorID;

                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_NETTING_AMOUNT, parm1);
                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToDecimal((rdr["NETTING_AMOUNT_PEDDING"] != DBNull.Value) ? rdr["NETTING_AMOUNT_PEDDING"] : 0);
                }
                else
                    return 0;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Lấy các lệnh đặt trong ngày của của nhà đầu tư
        /// </summary>
        /// <param name="investorID">mã nhà đầu tư</param>
        /// <returns>Danh sách các lệnh đặt</returns>
        public List<OrdersBoscModel> getOrders(decimal investorID)
        {
            List<OrdersBoscModel> orders = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ORDERS_BYINVESTORID, parm);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// Lấy các lệnh đặt trong ngày của của nhà đầu tư
        /// </summary>
        /// <param name="investorID">mã nhà đầu tư</param>
        /// <returns>Danh sách các lệnh đặt</returns>
        public List<OrdersBoscModel> getOrdersWaiting(decimal investorID)
        {
            List<OrdersBoscModel> orders = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ORDERSWAITING_BYINVESTORID, parm);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }
        }

        public List<OrdersBoscModel> getOrdersHistoryDetail(decimal investorID, DateTime fromDate, DateTime toDate, string orderType, string orderClass, string orderSide, string symbol, string ID, decimal priceLow, decimal priceHigh, decimal qttyLow, decimal qttyHigh)
        {
            string STR_QUERY = GET_ORDERSHISTORY_DETAIL_BYINVESTORID;
            if (toDate.Equals(fromDate))
                STR_QUERY += searchOrderSide(orderSide) + searchOrderClass(orderClass) + searchOrderType(orderType) + searchPrice(priceLow, priceHigh) + searchOrderNo(ID) + searchQtty(qttyLow, qttyHigh) + searchSymbol(symbol) + " AND to_char(HISTORY_DATE, 'YYYYMMDD')='" + fromDate.ToString("yyyyMMdd") + "'";
            else
                STR_QUERY += searchOrderSide(orderSide) + searchOrderClass(orderClass) + searchOrderType(orderType) + searchPrice(priceLow, priceHigh) + searchOrderNo(ID) + searchQtty(qttyLow, qttyHigh) + searchSymbol(symbol) + " AND HISTORY_DATE>=to_date(" + fromDate.ToString("yyyyMMdd") + ", 'YYYYMMDD') AND  HISTORY_DATE<=to_date(" + toDate.ToString("yyyyMMdd") + ", 'YYYYMMDD')+1";
            STR_QUERY += " ORDER BY ORDER_ID DESC";
            List<OrdersBoscModel> orders = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, STR_QUERY, parm);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }
        }

        private string searchOrderSide(string orderSide)
        {
            switch (orderSide)
            {
                case "BUY":
                    return " AND OORB='2'";
                case "SELL":
                    return " AND OORB='1'";
                default:
                    return String.Empty;
            }
        }

        private string searchOrderType(string orderType)
        {
            switch (orderType)
            {
                case "PLACE":
                    return " AND NORC='1'";
                case "CANCEL":
                    return " AND NORC='3'";
                default:
                    return String.Empty;
            }
        }

        private string searchOrderClass(string orderClass)
        {
            switch (orderClass)
            {
                case "NORMAL":
                    return " AND NORB_TEXT LIKE '%Gi%'";
                case "ATO":
                    return " AND NORB_TEXT LIKE '%ATO%'";
                case "ATC":
                    return " AND NORB_TEXT LIKE '%ATC%'";
                default:
                    return String.Empty;
            }
        }

        private string searchSymbol(string symbol)
        {
            if ((symbol == null) || (symbol.Length == 0))
                return String.Empty;
            else
                return " AND STOCK_CODE='" + symbol.Trim() + "'";
        }

        private string searchOrderNo(string orderID)
        {
            if ((orderID == null) || (orderID.Length == 0))
                return String.Empty;
            else
                return " AND ORDER_NO='" + orderID.Trim() + "'";
        }

        private string searchPrice(decimal lowerPrice, decimal higherPrice)
        {
            if (lowerPrice.Equals(higherPrice))
                if (higherPrice.Equals(0))
                    return String.Empty;
                else
                    return " AND ORDER_PRICE='" + lowerPrice.ToString() + "'";
            else
                return " AND (ORDER_PRICE>='" + lowerPrice.ToString() + "') AND (ORDER_PRICE <='" + higherPrice.ToString() + "')";
        }

        private string searchQtty(decimal lowerQtty, decimal higherQtty)
        {
            if (lowerQtty.Equals(higherQtty))
                if (higherQtty.Equals(0))
                    return String.Empty;
                else
                    return " AND ORDER_QTTY='" + lowerQtty.ToString() + "'";
            else
                return " AND (ORDER_QTTY>='" + lowerQtty.ToString() + "') AND (ORDER_QTTY <='" + higherQtty.ToString() + "')";
        }

        public List<OrdersBoscModel> getOrdersTopN(decimal investorID, int topN)
        {
            List<OrdersBoscModel> orders = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_TOPN, OleDbType.Integer);
                parm2.Value = topN;
                OleDbParameter[] parms = { parm1, parm2 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ORDERSHISTORY_TOPN_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    orders.Add(order);
                }
                rdr.Close();
                return orders;
            }
            catch
            {
                throw;
            }
        }

        public List<OrdersBoscModel> getOrdersHistory(decimal investorID, DateTime start, DateTime end)
        {
            List<OrdersBoscModel> ordersHistory = new List<OrdersBoscModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_STARTDATE, OleDbType.Date);
                parm2.Value = start;
                OleDbParameter parm3 = new OleDbParameter(PARM_ENDDATE, OleDbType.Date);
                parm3.Value = end;
                OleDbParameter[] parms = { parm1, parm2, parm3 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ORDERSHISTORY_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    OrdersBoscModel order = new OrdersBoscModel(Convert.ToDecimal(rdr["ORDER_ID"]).ToString(), VNConverter.TCVN3ToUnicode(rdr["OORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORC_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["NORB_TEXT"].ToString()), VNConverter.TCVN3ToUnicode(rdr["STATUS_TEXT"].ToString()), Convert.ToDateTime(rdr["ORDER_TIME"]), Convert.ToDecimal(rdr["ORDER_QTTY"]), Convert.ToDecimal(rdr["ORDER_PRICE"]), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["ORDER_FEE"]), rdr["ORG_ORDER_ID"].ToString());
                    ordersHistory.Add(order);
                }
                rdr.Close();
                return ordersHistory;
            }
            catch
            {
                throw;
            }
        }

        public List<MatchedResultModel> getMatchedResult(decimal investorID)
        {
            List<MatchedResultModel> matchedResult = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm.Value = investorID;
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MATCHEDRESULT_BYINVESTORID, parm);
                while (rdr.Read())
                {
                    MatchedResultModel result = new MatchedResultModel(rdr["CONFIRM_NO"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]), Convert.ToDecimal(rdr["FEE_AMOUNT"]), rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode(rdr["TRANS_TYPE_TEXT"].ToString()));
                    matchedResult.Add(result);
                }
                rdr.Close();
                return matchedResult;
            }
            catch
            {
                throw;
            }
        }

        public List<MatchedResultModel> getMatchedResultHistory(decimal investorID, DateTime start, DateTime end)
        {
            List<MatchedResultModel> matchedResultHistory = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_STARTDATE, OleDbType.Date);
                parm2.Value = start;
                OleDbParameter parm3 = new OleDbParameter(PARM_ENDDATE, OleDbType.Date);
                parm3.Value = end;
                OleDbParameter[] parms = { parm1, parm2, parm3 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MATCHEDRESULT_HISTORY_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    MatchedResultModel result = new MatchedResultModel(rdr["CONFIRM_NO"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]), Convert.ToDecimal(rdr["FEE_AMOUNT"]), rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode(rdr["TRANS_TYPE_TEXT"].ToString()));
                    matchedResultHistory.Add(result);
                }
                rdr.Close();
                return matchedResultHistory;
            }
            catch
            {
                throw;
            }
        }

        public List<MatchedResultModel> getMatchedResultTopN(decimal investorID, int topN)
        {
            List<MatchedResultModel> matchedResultHistory = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_TOPN, OleDbType.Integer);
                parm2.Value = topN;
                OleDbParameter[] parms = { parm1, parm2 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_MATCHEDRESULT_TOPN_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    MatchedResultModel result = new MatchedResultModel(rdr["CONFIRM_NO"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]), Convert.ToDecimal(rdr["FEE_AMOUNT"]), rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode(rdr["TRANS_TYPE_TEXT"].ToString()));
                    matchedResultHistory.Add(result);
                }
                rdr.Close();
                return matchedResultHistory;
            }
            catch
            {
                throw;
            }
        }
        /*
        public List<MatchedResultModel> getMatchedResultByInvestorId(decimal investorID)
        {
            List<MatchedResultModel> matchedResultHistory = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;


                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ALLMATCHEDRESULT_BY_INVESTORID, parm1);
                while (rdr.Read())
                {
                    MatchedResultModel result = new MatchedResultModel(rdr["CONFIRM_NO"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]) / 1000, Convert.ToDecimal(rdr["FEE_AMOUNT"]) / 1000, rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), VNConverter.TCVN3ToUnicode(rdr["TRANS_TYPE_TEXT"].ToString()));
                    matchedResultHistory.Add(result);
                }
                rdr.Close();
                return matchedResultHistory;
            }
            catch
            {
                throw;
            }
        }
        */

        public List<MatchedResultModel> getMatchedResultByInvestorId(decimal investorID)
        {
            List<MatchedResultModel> matchedResultHistory = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;


                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ALLMATCHEDRESULT_BY_INVESTORID, parm1);
                while (rdr.Read())
                {
                    MatchedResultModel result = null;
                    if (Convert.ToDecimal(rdr["TYPE"]).Equals(2))
                        result = new MatchedResultModel(rdr["ORDER_ID"].ToString(), rdr["CODE"].ToString(), Convert.ToDecimal(rdr["TRANS_QTTY"]), Convert.ToDecimal(rdr["TRANS_PRICE"]) / 1000, 0, rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), "Mua");
                    else
                        result = new MatchedResultModel(rdr["ORDER_ID"].ToString(), rdr["CODE"].ToString(), Convert.ToDecimal(rdr["TRANS_QTTY"]), Convert.ToDecimal(rdr["TRANS_PRICE"]) / 1000, 0, rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), "Bán");

                    matchedResultHistory.Add(result);
                }
                rdr.Close();
                return matchedResultHistory;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /*
        public List<MatchedResultModel> getMatchedResultByInvestorId(decimal investorID)
        {
            List<MatchedResultModel> matchedResultHistory = new List<MatchedResultModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;


                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ALLMATCHEDRESULT_BY_INVESTORID_2, parm1);
                while (rdr.Read())
                {
                    MatchedResultModel result = null;
                    if (Convert.ToDecimal(rdr["TRANS_TYPE"]).Equals(2))
                        result = new MatchedResultModel(rdr["ORDER_ID"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]) / 1000, 0, rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), "Mua");
                    else
                        result = new MatchedResultModel(rdr["ORDER_ID"].ToString(), rdr["STOCK_CODE"].ToString(), Convert.ToDecimal(rdr["STOCK_QTTY"]), Convert.ToDecimal(rdr["PRICE"]) / 1000, 0, rdr["ORDER_ID"].ToString(), Convert.ToDateTime(rdr["TRANS_DATE"]), "Bán");

                    matchedResultHistory.Add(result);
                }
                rdr.Close();
                return matchedResultHistory;
            }
            catch
            {
                throw;
            }
        }
        */
        public decimal getTradingAmountByAccountNo(string AccountNo)
        {
            OleDbDataReader rdr = null;
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_ACCOUNTNO, OleDbType.VarChar, 10);
                parm1.Value = AccountNo;
                OleDbParameter parm2 = new OleDbParameter(PARM_ACCOUNTNO, OleDbType.VarChar, 10);
                parm2.Value = AccountNo;
                OleDbParameter[] parms = { parm1, parm2 };

                rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BANKINFO_BYACCOUNTNO, parms);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToDecimal(rdr["TRADING_AMOUNT"]);
                }
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                rdr.Close();
            }
        }

        public int getTradingQuantityByStockCode(string AccountNo, string StockCode)
        {
            try
            {
                decimal investorid = getInvestorId(AccountNo);

                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm1.Value = investorid;
                OleDbParameter parm2 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm2.Value = investorid;
                OleDbParameter parm3 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm3.Value = investorid;

                OleDbParameter[] parms = { parm1, parm2, parm3 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_ACCOUNTINFO_BYINVESTORID, parms);
                while (rdr.Read())
                {
                    if (rdr["CODE"].ToString().Equals(StockCode))
                        return Convert.ToInt32(rdr["TRADING_QTTY"]);
                }
                rdr.Close();
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        private decimal getInvestorId(string accountNo)
        {
            OleDbDataReader rdr = null;
            try
            {
                OleDbParameter profileparm1 = new OleDbParameter(PARM_USERID, OleDbType.VarChar, 20);
                profileparm1.Value = accountNo;
                rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_INVESTORID_BYACCOUNTNO, profileparm1);
                if (rdr.Read())
                    return Convert.ToDecimal(rdr["INVESTOR_ID"]);
                else
                    return 0;
            }
            catch
            {
                throw;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<DateTime> getBackDate()
        {
            List<DateTime> backdate = new List<DateTime>();
            try
            {
                OleDbDataReader rdr = OleDbHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_BACK_DATE);
                while (rdr.Read())
                {
                    backdate.Add(Convert.ToDateTime(rdr["T1_date"]));
                    backdate.Add(Convert.ToDateTime(rdr["T2_date"]));
                    backdate.Add(Convert.ToDateTime(rdr["T3_date"]));
                    backdate.Add(Convert.ToDateTime(rdr["T4_date"]));
                }
                rdr.Close();
                return backdate;
            }
            catch
            {
                throw;
            }
        }

        public decimal getPreLoanByInvestor(decimal investorid)
        {
            OleDbDataReader rdr = null;
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal);
                parm1.Value = investorid;

                rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_PRE_LOAN, parm1);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    return Convert.ToDecimal(rdr["AMOUNT"]);
                }
                else
                    return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                rdr.Close();
            }
        }

        public List<AccountDataModel> getStockNettingInvestorId(decimal investorID, DateTime transdate)
        {
            List<AccountDataModel> accounts = new List<AccountDataModel>();
            try
            {
                OleDbParameter parm1 = new OleDbParameter(PARM_INVESTORID, OleDbType.Decimal, 20);
                parm1.Value = investorID;
                OleDbParameter parm2 = new OleDbParameter(PARM_INVESTORID, OleDbType.VarChar);
                parm2.Value = transdate.ToString("yyyyMMdd");

                OleDbParameter[] parms = { parm1, parm2 };

                OleDbDataReader rdr = OleDbHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_STOCKNETTING_BYINVESTORID, parms);

                while (rdr.Read())
                {
                    AccountDataModel account = new AccountDataModel(rdr["CODE"].ToString(), rdr["CODE"].ToString(), Convert.ToDecimal(rdr["CREDIT_QTTY"]), 0, 0, 0, DateTime.Today, DateTime.Today);
                    accounts.Add(account);
                }
                rdr.Close();
                return accounts;
            }
            catch
            {
                throw;
            }
        }

        public bool isClearingDone()
        {
            OleDbDataReader rdr1 = null;
            OleDbDataReader rdr2 = null;
            try
            {
                rdr1 = OleDbHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CALL_STATUS);
                rdr2 = OleDbHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_NUM_OF_CALL);
                rdr1.Read();
                rdr2.Read();
                return Convert.ToInt32(rdr1["CALL_STATUS"]) > Convert.ToInt32(rdr2["NUM_OF_CALL"]);
            }
            catch (System.Exception e)
            {
                throw e;
            }
            finally
            {
                rdr1.Close();
                rdr2.Close();
            }
        }


        #region IInvestor Members


        public AccountData getCustomerInfo(string AccountNo)
        {
            AccountData investor = null;
            try
            {
                OleDbParameter profileparm1 = new OleDbParameter("Account_No", OleDbType.VarChar);
                profileparm1.Value = AccountNo;

                OleDbDataReader rdr = OleDbHelper.ExecuteReaderSingleRowSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CUSTOMERDATA_BYACCOUNTNO, profileparm1);
                if (rdr.Read())
                {
                    investor = new AccountData(
                            VNConverter.TCVN3ToUnicode((rdr["NAME"] != DBNull.Value) ?
                                        rdr["NAME"].ToString() : String.Empty),
                                        (Convert.ToInt32(rdr["SEX"]).Equals(1)) ? "Nam" : "Nữ",
                                        rdr["ACCOUNT_NO"].ToString(),
                                        rdr["CARD_NO"].ToString(),
                                        string.IsNullOrEmpty(rdr["CARD_DATE"].ToString()) ? DateTime.Today : Convert.ToDateTime(rdr["CARD_DATE"]),

                            VNConverter.TCVN3ToUnicode((rdr["CARD_ISSUER"] != DBNull.Value) ?
                                        rdr["CARD_ISSUER"].ToString() : String.Empty),
                                        rdr["PHONE3"].ToString(),
                                        rdr["PHONE2"].ToString(),
                                        rdr["EMAIL"].ToString(),
                            VNConverter.TCVN3ToUnicode(rdr["ADDRESS1"].ToString()),
                            VNConverter.TCVN3ToUnicode(rdr["ADDRESS2"].ToString()));


                    rdr.Close();
                }
                return investor;
            }
            catch
            {
                throw;
            }
        }

        #endregion
    }
}